
from IPython.display import display, HTML

display(HTML(data="""
<style>
    div#notebook-container    { width: 95%; }
    div#menubar-container     { width: 65%; }
    div#maintoolbar-container { width: 99%; }a
</style>
"""))


import pandas as pd
#from pandas import Series, DataFrame
import numpy as np
import matplotlib.pyplot as plt 
import chardet

import folium as fm
from folium import Marker, GeoJson
from folium.plugins import MarkerCluster, HeatMap, StripePattern

import geopandas as gpd
from geopandas import GeoSeries
from shapely.geometry import Point, LineString


import branca as br


# Getting the character encoding
# Open the CSV file in binary read mode and read its content as bytes
base = open(r'../../_data/institutos1.csv', 'rb').read()

# Detect the character encoding of the CSV file using the chardet library
det = chardet.detect(base)

# Extract the detected character encoding from the detection result
charenc = det['encoding']
charenc

'ascii'


# Read the CSV file 'institutos1.csv' into a Pandas DataFrame using the detected character encoding
institutes = pd.read_csv(r'../../_data/institutos1.csv', encoding = charenc)
institutes


# Select specific columns from the 'institutes' DataFrame and store them in a new DataFrame named 'institutes_data'
institutes_data = institutes[['cod_mod', 'ltimoden_metaatencion', 'cuentadeid_postulante_procesoadm', 'sumaden_flagingresante', 'sumaden_flagmatriculado', 'nlat_ie', 'nlong_ie']]


# Rename the selected columns in the 'institutes_data' DataFrame to more descriptive names
institutes_data = institutes.rename({'cod_mod':'institute_code','ltimoden_metaatencion':'total_vacancies', 'cuentadeid_postulante_procesoadm':'total_applicants', 'sumaden_flagingresante':'total_entrants', 'sumaden_flagmatriculado':'total_enrolled', 'nlat_ie':'latitude', 'nlong_ie':'longitude'}, axis =1 )
institutes_data


list(institutes_data.columns)

['institute_code',
 'total_vacancies',
 'total_applicants',
 'total_entrants',
 'total_enrolled',
 'ratio',
 'ratio1',
 'dif',
 'dif1',
 'latitude',
 'longitude',
 'population_ccpp',
 'altitude',
 'Dblock1km',
 'Dblock2km',
 'Dblock3km',
 'Dblock5km',
 'Dblock10km',
 'Dblock20km',
 'Dblock30km',
 '_merge']


# Create a GeoDataFrame named 'institutes_geo'
# This GeoDataFrame will contain both the tabular data and geometric data
institutes_geo = gpd.GeoDataFrame(institutes_data,crs="EPSG:4326",
                          geometry=gpd.points_from_xy(institutes_data.longitude, institutes_data.latitude))


# Read the shapefile containing departmental boundaries into a GeoDataFrame
shapefile_dpto = gpd.read_file(r'../../_data/INEI_LIMITE_DEPARTAMENTAL/INEI_LIMITE_DEPARTAMENTAL.shp')
shapefile_dpto


# Perform spatial overlay operation to find the intersection of geometries
intersct_inst_geo = gpd.overlay(institutes_geo, shapefile_dpto, how="intersection")


# Variables: 'total_vacancies', 'total_applicants', 'total_entrants', 'total_enrolled'
vars_sum = ['total_vacancies', 'total_applicants', 'total_entrants','total_enrolled']


# Group the intersected GeoDataFrame by 'CCDD' (presumably a departmental code)
# and sum the variables in 'vars_sum' for each department
total_dpto = intersct_inst_geo.groupby(['CCDD'], as_index=False)[vars_sum].sum()

# Merge the aggregated data ('total_dpto') back into the departmental shapefile ('shapefile_dpto')
# using 'CCDD' as the common key
total_dpto_shp = shapefile_dpto.merge(total_dpto,on='CCDD')
total_dpto_shp


list(total_dpto_shp.columns)

['OBJECTID_1',
 'OBJECTID',
 'CCDD',
 'NOMBDEP',
 'CAPITAL',
 'Shape_STAr',
 'Shape_STLe',
 'ORIG_FID',
 'Shape_Leng',
 'Shape_Area',
 'CORREO',
 'CONTACTO',
 'WHATSAPP',
 'geometry',
 'total_vacancies',
 'total_applicants',
 'total_entrants',
 'total_enrolled']


# Specify the latitude and longitude of the Government Palace
lat_palacio = -12.0757538
long_palacio = -76.9863174

# Set the initial zoom level for the map
zoom_start = 5

# Create a Folium Map centered on the Government Palace location
map_graph = fm.Map(location = [lat_palacio, long_palacio], tiles='cartodbpositron', zoom_start = zoom_start)

# Add a choropleth layer to the map
fm.Choropleth(
    geo_data=shapefile_dpto,
    data=total_dpto_shp,
    columns=['CCDD', 'total_vacancies'],
    key_on="feature.properties.CCDD",
    fill_color="YlOrRd",
    fill_opacity=0.8,
    line_opacity=0.2,
    legend_name="TOTAL INSTITUTE VACANCIES BY DEPARTMENT",
    smooth_factor=0,
    Highlight= True,
    line_color = "#0000",
    overlay=True,
    nan_fill_color = "White"  
    ).add_to(map_graph)

# Display the map
map_graph


def visual_html(i):   
    # Extract data for the i-th row from the GeoDataFrame intersct_inst_geo
    Total_Vacancies = intersct_inst_geo['total_vacancies'].iloc[i]                             
    Total_Applicants = intersct_inst_geo['total_applicants'].iloc[i]                           
    Total_Entrants = intersct_inst_geo['total_entrants'].iloc[i]  
    Total_Enrolled = intersct_inst_geo['total_enrolled'].iloc[i]  
    
    # Define colors for left and right columns
    left_col_colour = "#76db37" 
    right_col_colour = "#5ad2d6"
    
    # Create HTML string for the table
    html = """<!DOCTYPE html>
<html>

<head>
    <p> Institutes Data by Department </p>
</head>

<table style="height: 126px; width: 350px;">  <!-- Comment: Create a table. -->
    <tbody> 
"""

    # Define row labels and corresponding data
    row_labels = ["Total Vacancies", "Total Applicants", "Total Entrants", "Total Enrolled"]
    data_values = [Total_Vacancies, Total_Applicants, Total_Entrants, Total_Enrolled]

    # Loop through each row label and data value to generate table rows
    for label, value in zip(row_labels, data_values):
        # Alternate colors for left and right columns
        if label == "Total Vacancies" or label == "Total Entrants":
            left_col_colour = "#76db37"
            right_col_colour = "#5ad2d6"
        else:
            left_col_colour = "#76db37"
            right_col_colour = "#5ad2d6"
        
        # Add row to HTML string
        html += f"""
        <tr>
            <td style="background-color: {left_col_colour};"><span style="color: #1a1719;">{label}</span></td>
            <td style="width: 150px;background-color: {right_col_colour};">{value}</td>
        </tr>
        """

    # Close the HTML table and document tags
    html += """
    </tbody>
</table>
</html>
"""
    return html


lat_palacio = -12.0757538
long_palacio = -76.9863174
zoom_start = 5

map_graph2 = fm.Map(location = [lat_palacio, long_palacio], tiles='OpenStreetMap', zoom_start = zoom_start)

# Iterate over each institute in the intersct_inst_geo GeoDataFrame
for i in range(0,len(intersct_inst_geo)):
    # Generate HTML for the current marker using the visual_html() function
    html = visual_html(i)
    
    # Create an IFrame with the generated HTML
    iframe = br.element.IFrame(html=html,width=350,height=300)
    popup = fm.Popup(iframe, parse_html=True)
    
    # Add a marker to the map at the location of the current institute,
    # with the popup containing detailed information
    fm.Marker([intersct_inst_geo['latitude'].iloc[i],intersct_inst_geo['longitude'].iloc[i]],
                  popup=popup, icon=fm.Icon(color= 'blue', icon='university', prefix="fa")).add_to(map_graph2)

map_graph2


# Calculate the ratio of total applicants to total vacancies and create a new column 'ratio_applicants_vacancies'
intersct_inst_geo['ratio_applicants_vacancies'] = intersct_inst_geo['total_applicants']/intersct_inst_geo['total_vacancies']


# Select institutes with a surplus of applicants (ratio >= 1)
applicant_surplus = intersct_inst_geo[intersct_inst_geo['ratio_applicants_vacancies']>=1]

# Select institutes with a deficit of applicants (ratio < 1)
applicant_deficit = intersct_inst_geo[intersct_inst_geo['ratio_applicants_vacancies']<1]


# Selecting specific columns from the DataFrame 'applicant_surplus'
# and storing them in a new DataFrame
applicant_surplus = applicant_surplus.loc[:, ['institute_code', 'total_vacancies', 'total_applicants', 'total_entrants', 'total_enrolled', 'latitude', 'longitude', 'ratio_applicants_vacancies']]
applicant_surplus


# Create a list of tuples containing latitude and longitude coordinates from 'applicant_surplus'
applit_surplus_list = list(zip(applicant_surplus['latitude'], applicant_surplus['longitude']))

# Create a list of tuples containing latitude and longitude coordinates from 'applicant_deficit'
applit_deficit_list = list(zip(applicant_deficit['latitude'], applicant_deficit['longitude']))


# Create a map centered on the coordinates of the Government Palace
map_graph3 = fm.Map(location = [lat_palacio, long_palacio], zoom_start = 5)

# Add MarkerCluster for institutes with applicant surplus and applicant deficit
MarkerCluster( applit_surplus_list, name = 'Applicant Surplus' ).add_to(map_graph3) 
MarkerCluster( applit_deficit_list, name = 'Applicant Deficit' ).add_to(map_graph3)

# Add layer control to toggle between marker clusters
fm.LayerControl(collapsed=False).add_to(map_graph3)

# Display the map
map_graph3


# Create a copy of the DataFrame 'applicant_surplus'
applicant_surplus = applicant_surplus.copy()

# Calculate the radius values and assign them to the new 'radius' column
applicant_surplus.loc[:, 'radius'] = applicant_surplus['ratio_applicants_vacancies'] * 10000


# Create a map centered on the coordinates of the Government Palace
map_graph4 = fm.Map(location = [lat_palacio, long_palacio], zoom_start = 5)

# Create a feature group for applicant surplus, with control enabled
feature_surplus = fm.FeatureGroup( name = 'Applicant Surplus', control = True )

# Iterate over each row in the DataFrame 'applicant_surplus'
for _, row in applicant_surplus.iterrows():
    
    fm.Circle([row['latitude'], row['longitude']],
              radius = row['radius'],
              fill=True,
              color="#1a823b").add_to(feature_surplus)

# Add the feature group to the map
map_graph4.add_child(feature_surplus)    

# Add layer control to toggle between feature groups
fm.LayerControl(collapsed=False).add_to(map_graph4)

# Display the map
map_graph4


# Create a copy of the DataFrame 'applicant_deficit'
applicant_deficit = applicant_deficit.copy()

# Calculate the radius values and assign them to the new 'radius' column
applicant_deficit.loc[:, 'radius'] = applicant_deficit['ratio_applicants_vacancies'] * 10000


# Create a map centered on the coordinates of the Government Palace
map_graph5 = fm.Map(location = [lat_palacio, long_palacio], zoom_start = 5)

# Create a feature group for applicant deficit, with control enabled
feature_deficit = fm.FeatureGroup( name = 'Applicant Deficit', control = True )

# Iterate over each row in the DataFrame 'applicant_deficit'
for _, row in applicant_deficit.iterrows():
    
    fm.Circle([row['latitude'], row['longitude']],
              radius = row['radius'],
              fill=True,
              color="#e63c3c").add_to(feature_deficit)

# Add the feature group to the map    
map_graph5.add_child(feature_deficit) 

# Add layer control to toggle between feature groups
fm.LayerControl(collapsed=False).add_to(map_graph5)

# Display the map
map_graph5


final_map_graph= fm.Map(location = [lat_palacio, long_palacio], zoom_start = 5)

# FeatureGroups for applicant surplus and deficit
feature_surplus = fm.FeatureGroup(name='Applicant Surplus', control=True)
feature_deficit = fm.FeatureGroup(name='Applicant Deficit', control=True)

# Add circles for institutes with surplus of applicants
for _, row in applicant_surplus.iterrows():
    
    fm.Circle([row['latitude'], row['longitude']],
              radius = row['radius'],
              fill=True,
              color="#1a823b").add_to(feature_surplus)

# Add circles for institutes with deficit of applicants    
for _, row in applicant_deficit.iterrows():
    
    fm.Circle([row['latitude'], row['longitude']],
              radius = row['radius'],
              fill=True,
              color="#e63c3c").add_to(feature_deficit)


final_map_graph.add_child( feature_surplus ) 
final_map_graph.add_child( feature_deficit ) 

legend_html_0 = '''
<div style="
position: fixed; 
bottom: 10px;
left: 10px;
width: 200px;
height: 120px;
z-index:9999;
font-size:14px;
<p><a style="color:black;font-size:100%;margin-left:5px;">&#9658;</a>&emsp;<b>Legend</b></p>
<p><a style="color:green;font-size:100%;margin-left:5px;">&#9679;</a>&emsp;<b>Applicant Surplus</b></p>
<p><a style="color:red;font-size:100%;margin-left:5px;">&#9679;</a>&emsp;<b>Applicant Deficit</b></p>

</div>
<div style="
position: fixed; 
bottom: 10px;
left: 10px;
width: 200px;
height: 120px; 
z-index:9998;
font-size:14px;
background-color: #ffffff;

opacity: 0.7;
">
</div>
'''


# Create and add legend to the map
legend_html = "{% macro html(this, kwargs) %}" + legend_html_0 + "{% endmacro %}"
legend = br.element.MacroElement()
legend._template = br.element.Template(legend_html)
final_map_graph.get_root().add_child(legend)

# Add layer control to the map
fm.LayerControl(collapsed=False).add_to(final_map_graph)

final_map_graph

	cod_mod	ltimoden_metaatencion	cuentadeid_postulante_procesoadm	sumaden_flagingresante	sumaden_flagmatriculado	ratio	ratio1	dif	dif1	nlat_ie	...	population_ccpp	altitude	Dblock1km	Dblock2km	Dblock3km	Dblock5km	Dblock10km	Dblock20km	Dblock30km	_merge
0	207597	307	481	302	301	0.980456	1.566775	-6	174	-13.52107	...	106400.0	3386.0	14840369	742184	494123	296073	148036	7418	4912	matched (3)
1	207613	240	502	204	204	0.850000	2.091667	-36	262	-13.63997	...	45864.0	2342.0	14970470	748235	499156	299094	149047	7423	4915	matched (3)
2	239970	537	1047	484	476	0.886406	1.949721	-61	510	-15.47827	...	216716.0	3831.0	17000163	850081	566054	340032	170016	8508	5605	matched (3)
3	262311	423	526	353	349	0.825059	1.243499	-74	103	-6.23041	...	23202.0	2358.0	6781025	339512	226341	135205	67102	3351	2234	matched (3)
4	273979	425	544	411	405	0.952941	1.280000	-20	119	-6.49004	...	67362.0	309.0	7070857	353428	235285	141171	70085	3542	2328	matched (3)
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
210	1468594	1566	250	241	202	0.128991	0.159642	-1364	-1316	-12.02987	...	579561.0	125.0	13190935	659467	439311	263187	131093	6546	4331	matched (3)
211	1468610	284	74	71	71	0.250000	0.260563	-213	-210	-12.08215	...	66171.0	115.0	13250934	662467	441311	265186	132093	6646	4431	matched (3)
212	1545623	210	266	184	177	0.842857	1.266667	-33	56	-16.13940	...	1175.0	3780.0	17740208	887104	591069	354041	177020	8810	5906	matched (3)
213	1595495	206	144	141	137	0.665049	0.699029	-69	-62	-18.01370	...	86095.0	577.0	19810177	990088	660059	396035	198017	9908	6605	matched (3)
214	1637859	180	134	125	125	0.694444	0.744444	-55	-46	-13.63631	...	45864.0	2392.0	14970469	748234	499156	299093	149046	7423	4915	matched (3)

	institute_code	total_vacancies	total_applicants	total_entrants	total_enrolled	ratio	ratio1	dif	dif1	latitude	...	population_ccpp	altitude	Dblock1km	Dblock2km	Dblock3km	Dblock5km	Dblock10km	Dblock20km	Dblock30km	_merge
0	207597	307	481	302	301	0.980456	1.566775	-6	174	-13.52107	...	106400.0	3386.0	14840369	742184	494123	296073	148036	7418	4912	matched (3)
1	207613	240	502	204	204	0.850000	2.091667	-36	262	-13.63997	...	45864.0	2342.0	14970470	748235	499156	299094	149047	7423	4915	matched (3)
2	239970	537	1047	484	476	0.886406	1.949721	-61	510	-15.47827	...	216716.0	3831.0	17000163	850081	566054	340032	170016	8508	5605	matched (3)
3	262311	423	526	353	349	0.825059	1.243499	-74	103	-6.23041	...	23202.0	2358.0	6781025	339512	226341	135205	67102	3351	2234	matched (3)
4	273979	425	544	411	405	0.952941	1.280000	-20	119	-6.49004	...	67362.0	309.0	7070857	353428	235285	141171	70085	3542	2328	matched (3)
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
210	1468594	1566	250	241	202	0.128991	0.159642	-1364	-1316	-12.02987	...	579561.0	125.0	13190935	659467	439311	263187	131093	6546	4331	matched (3)
211	1468610	284	74	71	71	0.250000	0.260563	-213	-210	-12.08215	...	66171.0	115.0	13250934	662467	441311	265186	132093	6646	4431	matched (3)
212	1545623	210	266	184	177	0.842857	1.266667	-33	56	-16.13940	...	1175.0	3780.0	17740208	887104	591069	354041	177020	8810	5906	matched (3)
213	1595495	206	144	141	137	0.665049	0.699029	-69	-62	-18.01370	...	86095.0	577.0	19810177	990088	660059	396035	198017	9908	6605	matched (3)
214	1637859	180	134	125	125	0.694444	0.744444	-55	-46	-13.63631	...	45864.0	2392.0	14970469	748234	499156	299093	149046	7423	4915	matched (3)

	OBJECTID_1	OBJECTID	CCDD	NOMBDEP	CAPITAL	Shape_STAr	Shape_STLe	ORIG_FID	Shape_Leng	Shape_Area	CORREO	CONTACTO	WHATSAPP	geometry
0	1	1.0	01	AMAZONAS	CHACHAPOYAS	3.203006	12.912088	0	12.912088	3.203006	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-77.81399 -2.99278, -77.81483 -2.995...
1	2	2.0	02	ANCASH	HUARAZ	2.954592	11.780424	1	11.780424	2.954592	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-77.64697 -8.05086, -77.64689 -8.051...
2	3	3.0	03	APURIMAC	ABANCAY	1.765933	7.730154	2	7.730154	1.765933	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-73.74655 -13.17442, -73.74570 -13.1...
3	4	4.0	04	AREQUIPA	AREQUIPA	5.330203	17.405040	3	17.405040	5.330203	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-71.98109 -14.64062, -71.98093 -14.6...
4	5	5.0	05	AYACUCHO	AYACUCHO	3.643705	17.127166	4	17.127166	3.643705	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-74.34843 -12.17503, -74.35000 -12.1...
5	6	6.0	06	CAJAMARCA	CAJAMARCA	2.684527	12.397424	5	12.397424	2.684527	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-78.22182 -7.76346, -78.22233 -7.763...
6	7	7.0	07	CALLAO	CALLAO	0.011738	1.111221	6	1.111221	0.011738	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-77.13504 -11.81820, -77.13484 -11.8...
7	8	8.0	08	CUSCO	CUSCO	6.000331	21.794434	7	21.794434	6.000331	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-72.97280 -11.25189, -72.97134 -11.2...
8	9	9.0	09	HUANCAVELICA	HUANCAVELICA	1.839851	9.561245	8	9.561245	1.839851	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-74.57118 -11.98710, -74.57095 -11.9...
9	10	10.0	10	HUANUCO	HUANUCO	3.089811	14.589840	9	14.589840	3.089811	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-75.99200 -8.32108, -75.99015 -8.321...
10	11	11.0	11	ICA	ICA	1.767025	10.374368	10	10.374368	1.767025	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-75.61136 -12.96649, -75.61103 -12.9...
11	12	12.0	12	JUNIN	HUANCAYO	3.658881	14.314727	11	14.314727	3.658881	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-75.03564 -10.68232, -75.03436 -10.6...
12	13	13.0	13	LA LIBERTAD	TRUJILLO	2.068780	13.148266	12	13.148266	2.068780	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-77.75753 -6.96579, -77.75638 -6.967...
13	14	14.0	14	LAMBAYEQUE	CHICLAYO	1.184994	6.685351	13	6.685351	1.184994	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-79.88373 -5.49283, -79.88265 -5.494...
14	15	15.0	15	LIMA	LIMA	2.904475	12.745226	14	12.745226	2.904475	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	MULTIPOLYGON (((-76.79732 -12.47686, -76.79732...
15	19	16.0	16	LORETO	IQUITOS	30.550256	46.701789	15	46.701789	30.550256	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-75.10004 -0.08096, -75.09485 -0.081...
16	20	17.0	17	MADRE DE DIOS	PUERTO MALDONADO	7.023854	15.416397	16	15.416397	7.023854	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-70.61346 -10.12964, -70.61359 -10.2...
17	21	18.0	18	MOQUEGUA	MOQUEGUA	1.334888	7.720424	17	7.720424	1.334888	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-70.79388 -15.98721, -70.79486 -15.9...
18	22	19.0	19	PASCO	CERRO DE PASCO	1.972864	10.891698	18	10.891698	1.972864	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-74.68005 -9.44727, -74.67872 -9.448...
19	23	20.0	20	PIURA	PIURA	2.899223	11.299471	19	11.299471	2.899223	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-81.02621 -4.10594, -81.02557 -4.106...
20	24	21.0	21	PUNO	PUNO	5.666684	26.709721	20	26.709721	5.666684	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	MULTIPOLYGON (((-68.81422 -16.34799, -68.91839...
21	31	22.0	22	SAN MARTIN	MOYOBAMBA	4.174664	16.162954	21	16.162954	4.174664	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-77.71678 -5.41597, -77.71270 -5.416...
22	32	23.0	23	TACNA	TACNA	1.362022	6.088704	22	6.088704	1.362022	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-70.26604 -16.77955, -70.26474 -16.7...
23	33	24.0	24	TUMBES	TUMBES	0.378757	3.787977	23	3.787977	0.378757	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-80.28521 -3.41276, -80.28406 -3.412...
24	34	25.0	25	UCAYALI	PUCALLPA	8.660111	27.470159	24	27.470159	8.660111	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-74.47145 -7.27617, -74.47052 -7.277...

	OBJECTID_1	OBJECTID	CCDD	NOMBDEP	CAPITAL	Shape_STAr	Shape_STLe	ORIG_FID	Shape_Leng	Shape_Area	CORREO	CONTACTO	WHATSAPP	geometry	total_vacancies	total_applicants	total_entrants	total_enrolled
0	1	1.0	01	AMAZONAS	CHACHAPOYAS	3.203006	12.912088	0	12.912088	3.203006	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-77.81399 -2.99278, -77.81483 -2.995...	1213	1346	925	917
1	2	2.0	02	ANCASH	HUARAZ	2.954592	11.780424	1	11.780424	2.954592	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-77.64697 -8.05086, -77.64689 -8.051...	2969	4007	2535	2501
2	3	3.0	03	APURIMAC	ABANCAY	1.765933	7.730154	2	7.730154	1.765933	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-73.74655 -13.17442, -73.74570 -13.1...	1647	2997	1506	1506
3	4	4.0	04	AREQUIPA	AREQUIPA	5.330203	17.405040	3	17.405040	5.330203	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-71.98109 -14.64062, -71.98093 -14.6...	3000	2829	1976	1941
4	5	5.0	05	AYACUCHO	AYACUCHO	3.643705	17.127166	4	17.127166	3.643705	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-74.34843 -12.17503, -74.35000 -12.1...	3107	3824	2406	2374
5	6	6.0	06	CAJAMARCA	CAJAMARCA	2.684527	12.397424	5	12.397424	2.684527	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-78.22182 -7.76346, -78.22233 -7.763...	5943	7744	5104	5068
6	7	7.0	07	CALLAO	CALLAO	0.011738	1.111221	6	1.111221	0.011738	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-77.13504 -11.81820, -77.13484 -11.8...	780	589	509	506
7	8	8.0	08	CUSCO	CUSCO	6.000331	21.794434	7	21.794434	6.000331	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-72.97280 -11.25189, -72.97134 -11.2...	6441	6575	4758	4694
8	9	9.0	09	HUANCAVELICA	HUANCAVELICA	1.839851	9.561245	8	9.561245	1.839851	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-74.57118 -11.98710, -74.57095 -11.9...	705	1694	705	646
9	10	10.0	10	HUANUCO	HUANUCO	3.089811	14.589840	9	14.589840	3.089811	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-75.99200 -8.32108, -75.99015 -8.321...	1599	2360	1482	1463
10	11	11.0	11	ICA	ICA	1.767025	10.374368	10	10.374368	1.767025	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-75.61136 -12.96649, -75.61103 -12.9...	2882	1451	1248	1199
11	12	12.0	12	JUNIN	HUANCAYO	3.658881	14.314727	11	14.314727	3.658881	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-75.03564 -10.68232, -75.03436 -10.6...	4864	4042	3075	2945
12	13	13.0	13	LA LIBERTAD	TRUJILLO	2.068780	13.148266	12	13.148266	2.068780	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-77.75753 -6.96579, -77.75638 -6.967...	5708	4387	3230	3132
13	14	14.0	14	LAMBAYEQUE	CHICLAYO	1.184994	6.685351	13	6.685351	1.184994	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-79.88373 -5.49283, -79.88265 -5.494...	2671	1936	1769	1752
14	15	15.0	15	LIMA	LIMA	2.904475	12.745226	14	12.745226	2.904475	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	MULTIPOLYGON (((-76.79732 -12.47686, -76.79732...	20885	9159	7847	6832
15	19	16.0	16	LORETO	IQUITOS	30.550256	46.701789	15	46.701789	30.550256	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-75.10004 -0.08096, -75.09485 -0.081...	2106	3249	1841	1709
16	20	17.0	17	MADRE DE DIOS	PUERTO MALDONADO	7.023854	15.416397	16	15.416397	7.023854	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-70.61346 -10.12964, -70.61359 -10.2...	292	307	257	266
17	21	18.0	18	MOQUEGUA	MOQUEGUA	1.334888	7.720424	17	7.720424	1.334888	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-70.79388 -15.98721, -70.79486 -15.9...	508	566	452	431
18	22	19.0	19	PASCO	CERRO DE PASCO	1.972864	10.891698	18	10.891698	1.972864	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-74.68005 -9.44727, -74.67872 -9.448...	802	964	692	680
19	23	20.0	20	PIURA	PIURA	2.899223	11.299471	19	11.299471	2.899223	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-81.02621 -4.10594, -81.02557 -4.106...	1010	1094	759	755
20	24	21.0	21	PUNO	PUNO	5.666684	26.709721	20	26.709721	5.666684	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	MULTIPOLYGON (((-68.81422 -16.34799, -68.91839...	4455	5114	2904	2798
21	31	22.0	22	SAN MARTIN	MOYOBAMBA	4.174664	16.162954	21	16.162954	4.174664	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-77.71678 -5.41597, -77.71270 -5.416...	1747	2327	1660	1649
22	32	23.0	23	TACNA	TACNA	1.362022	6.088704	22	6.088704	1.362022	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-70.26604 -16.77955, -70.26474 -16.7...	846	887	667	662
23	33	24.0	24	TUMBES	TUMBES	0.378757	3.787977	23	3.787977	0.378757	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-80.28521 -3.41276, -80.28406 -3.412...	390	483	336	223
24	34	25.0	25	UCAYALI	PUCALLPA	8.660111	27.470159	24	27.470159	8.660111	juan.suyo@geogpsperu.com	www.geogpsperu.com	931381206	POLYGON ((-74.47145 -7.27617, -74.47052 -7.277...	907	1200	754	744

	institute_code	total_vacancies	total_applicants	total_entrants	total_enrolled	latitude	longitude	ratio_applicants_vacancies
0	207597	307	481	302	301	-13.52107	-71.97748	1.566775
1	586669	516	1047	515	544	-14.14240	-71.40813	2.029070
2	591909	180	297	169	169	-13.30178	-72.12163	1.650000
3	783399	220	404	198	198	-14.28296	-71.22042	1.836364
4	928655	322	342	290	284	-13.51273	-71.97511	1.062112
...	...	...	...	...	...	...	...	...
203	695452	880	1523	791	787	-16.36050	-71.54370	1.730682
205	891952	110	139	110	107	-16.38784	-71.54900	1.263636
208	1116136	170	196	152	152	-17.02406	-72.00455	1.152941
212	1192830	300	304	242	242	-12.01212	-77.10461	1.013333
214	1275759	390	483	336	223	-3.55727	-80.44333	1.238462

Assignment 9¶

Installation and import of libraries¶

Part 1¶

1.1 Import the data located at this link. It has information on Tech Institutes' total vacancies, total applicants, total entrants, and total enrolled. Moreover, the institutes are geolocated.¶

1.2 Use this shapefile to generate a Choropleth map of the total institutes' vacancies by the department. Use the overlay function (link and JN) to intersect institutes dataset with shapefiles. You can follow this code:¶

Part 2¶

2.2 Additionally, use "university" as icon.¶

Part 3¶

3.1 Generate the ratio of applicants by vacancies and store it in a new columns called ratio_applicants_vacancies (total applicants / total vacancies)¶

Part 4¶

4.1 Generate a Circle map and plot only institutes with more applicants than vacancies. Make the radius= ratio_applicants_vacancies * 10000¶

4.2 Make these circles of color green. Name the layer of circles as Applicant Surplus. Use FeatureGroup¶

Part 5¶

5.1 Generate a Circle map and plot only institutes with less applicants than vacancies. Make the radius= ratio_applicants_vacancies * 10000¶

5.2 Make these circles of color red. Name the layer of circles as Applicant Deficit. Use FeatureGroup¶

Part 6¶

6.1 Generate a Circle Map that combines map 4 and 5 into a unique map¶

6.2 Add FeatureGroups to the map¶

6.3 Additionally, generate a legend for this map indicating the color and label.¶

Assignment 9¶

Installation and import of libraries¶

Part 1¶

1.1 Import the data located at this link. It has information on Tech Institutes' total vacancies, total applicants, total entrants, and total enrolled. Moreover, the institutes are geolocated.¶

1.2 Use this shapefile to generate a Choropleth map of the total institutes' vacancies by the department. Use the overlay function (link and JN) to intersect institutes dataset with shapefiles. You can follow this code:¶

Part 2¶

2.1 Make an interactive map of markes using visual_html function and show total vacancies, applicants, entrants, and enrolled information in a table popup.¶

2.2 Additionally, use "university" as icon.¶

Part 3¶

3.1 Generate the ratio of applicants by vacancies and store it in a new columns called ratio_applicants_vacancies (total applicants / total vacancies)¶

Part 4¶

4.1 Generate a Circle map and plot only institutes with more applicants than vacancies. Make the radius= ratio_applicants_vacancies * 10000¶

4.2 Make these circles of color green. Name the layer of circles as Applicant Surplus. Use FeatureGroup¶

Part 5¶

5.1 Generate a Circle map and plot only institutes with less applicants than vacancies. Make the radius= ratio_applicants_vacancies * 10000¶

5.2 Make these circles of color red. Name the layer of circles as Applicant Deficit. Use FeatureGroup¶

Part 6¶

6.1 Generate a Circle Map that combines map 4 and 5 into a unique map¶

6.2 Add FeatureGroups to the map¶

6.3 Additionally, generate a legend for this map indicating the color and label.¶